package org.aceor.mddal.unit.app;

import com.google.common.base.Preconditions;
import com.google.common.collect.Lists;
import org.aceor.mddal.unit.app.domain.SimpleStudent;
import org.aceor.mddal.common.utils.DateUtil;
import org.aceor.mddal.config.sharding.LogicTable;
import org.aceor.mddal.gateway.router.Router;
import org.aceor.mddal.sequence.SequenceUtil;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;
import java.sql.*;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Random;

import static org.aceor.mddal.common.utils.CommonUtil.forEachPrint;
import static org.aceor.mddal.common.utils.CommonUtil.print;
import static org.junit.Assert.*;

/**
 * Created by lxue on 16/4/13.
 */
public class Main {
    ApplicationContext applicationContext;
    Router router;
    JdbcTemplate jdbcTemplate;
    JdbcTemplate jdbcTemplateWithDBCP;
    static Random random = new Random();

    @Before
    public void init() {
        applicationContext = new ClassPathXmlApplicationContext("spring/applicationContext.xml");
        Preconditions.checkNotNull(applicationContext);
        router = Router.getInstance(applicationContext.getBean(LogicTable.class));
        Preconditions.checkNotNull(router);
        jdbcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);
        Preconditions.checkNotNull(jdbcTemplate);
        jdbcTemplateWithDBCP = applicationContext.getBean("jdbcTemplateWithDBCP", JdbcTemplate.class);
        Preconditions.checkNotNull(jdbcTemplateWithDBCP);
    }

    @Test
    public void test_common_resultset_using() {
        String sql = "select * from t_user";
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/kuaidi", "root", "software23");
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            assertNotNull(resultSet);
            forEachPrint(Arrays.asList(resultSet));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void test_dbcp_resultset_using() {
        String sql = "select * from t_user";
        try {
            DataSource dataSource = applicationContext.getBean(BasicDataSource.class);

            Statement statement = dataSource.getConnection().createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            print(resultSet.getClass());
            assertNotNull(resultSet);
            forEachPrint(Arrays.asList(resultSet));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void test_jdbcTemplate_直接使用dbcp数据源() throws SQLException {
        String sql = "select * from t_simple_stu where id IN (1,2,4) or id = 3";
        List<SimpleStudent> simpleStudents = jdbcTemplate.query(sql, new RowMapper<SimpleStudent>() {
            @Override
            public SimpleStudent mapRow(ResultSet resultSet, int i) throws SQLException {
                SimpleStudent simpleStudent = new SimpleStudent();
                simpleStudent.setId(resultSet.getLong("id"));
                simpleStudent.setName(resultSet.getString("name"));
                return simpleStudent;
            }
        });
        assertTrue(CollectionUtils.isNotEmpty(simpleStudents));
        //暂时还未合并
        assertEquals(2, simpleStudents.size());
    }

    @Test
    public void test_jdbcTemplate_仅分库() throws SQLException {
        //Condition1
        String sql = "select * from t_simple_stu where id IN (1,2,3,4)";
        List<SimpleStudent> simpleStudents = jdbcTemplate.query(sql, new RowMapper<SimpleStudent>() {
            @Override
            public SimpleStudent mapRow(ResultSet resultSet, int i) throws SQLException {
                SimpleStudent simpleStudent = new SimpleStudent();
                simpleStudent.setId(resultSet.getLong("id"));
                simpleStudent.setName(resultSet.getString("name"));
                return simpleStudent;
            }
        });

//        List<SimpleStudent> simpleStudents = forEachPrint(jdbcTemplate.query(sql, new ResultSetExtractor<List<SimpleStudent>>() {
//            @Override
//            public List<SimpleStudent> extractData(ResultSet resultSet) throws SQLException, DataAccessException {
//                List<SimpleStudent> simpleStudents = Lists.newLinkedList();
//                while (resultSet.next()) {
//                    SimpleStudent simpleStudent = new SimpleStudent();
//                    simpleStudent.setId(resultSet.getInt("id"));
//                    simpleStudent.setName(resultSet.getString("name"));
//                    simpleStudents.add(simpleStudent);
//                }
//                return simpleStudents;
//            }
//        }));
        forEachPrint(simpleStudents);
        assertNotNull(simpleStudents);
        assertTrue(CollectionUtils.isNotEmpty(simpleStudents));
    }

    @Test
    public void test_jdbcTemplate_仅分表() {
        //Condition2
        String sql = "select * from t_simple_stu where id IN (1,2,3,4)";
        try {
            List<SimpleStudent> simpleStudents = queryForSimpleStudentList(sql);
            forEachPrint(simpleStudents);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void test_jdbcTemplate_分库分表不同字段() {
        //Condition3
        /**
         *  按id分表,按age分库
         *  分库 >25的为1,<=25的为0
         *  分表 %2 t_0,t_1
         */
        try {
            String sql = "select * from t_simple_stu stu where t_simple_stu.age IN (21,25,27) and id IN (1,2,3,7,8)";
            List<SimpleStudent> simpleStudents = queryForSimpleStudentList(sql);
            assertTrue(CollectionUtils.isNotEmpty(simpleStudents));
            forEachPrint(simpleStudents);
        } catch (SQLException e) {
            ExceptionUtils.printRootCauseStackTrace(e);
        }
    }

    @Test
    public void test_jdbcTemplate_分库分表相同字段() {
        //Condition4
        /**
         *  按id分库分表
         *  分库 %2 group_0,group_1
         *  分表 %4 t_0,t_1,t_2,t_3
         */
        try {
            String sql = "select * from t_simple_stu stu where t_simple_stu.id between 3 and 11";
            List<SimpleStudent> simpleStudents = queryForSimpleStudentList(sql);
            assertTrue(CollectionUtils.isNotEmpty(simpleStudents));
            forEachPrint(simpleStudents);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void test_jdbcTemplate_分库分表相同字段_preparestatement() {
        //Condition4
        /**
         *  按id分库分表
         *  分库 %2 group_0,group_1
         *  分表 %4 t_0,t_1,t_2,t_3
         */
        try {
            String sql = "select count(id),age from t_simple_stu stu where (id between 12 and 15 or id IN(1,2,16)) group by age";
            List<SimpleStudent> simpleStudents = queryForSimpleStudentList(sql, Arrays.asList(2, 13, 11));
            assertTrue(CollectionUtils.isNotEmpty(simpleStudents));
            forEachPrint(simpleStudents);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void test_update_分库分表相同字段() {
        String sql = "update t_simple_stu SET age = ?, name = 'haibao', birth='1993-11-10', gmt_create='2016-04-14 23:00:00' where id = 8";
        int updates = jdbcTemplate.update(sql, 101);
        System.out.println(updates);
    }

    @Test
    public void test_insert_分库分表相同字段() {

        String sql = "insert t_simple_stu(id, age, name , birth, gmt_create) VALUES (?,?,?,?,?)";
        int max = 1460000;
        long currentTime = System.currentTimeMillis();
        for (int i = 0; i < max; i++) {
            Long id = SequenceUtil.generateUniqueSequenceNum();
            Object[] args = Arrays.asList(id, random.nextInt(70) + 6, "xuelei" + random.nextInt(1000), generateDate(), DateUtil.date2TimestampString(new Date())).toArray();
//            System.out.println("args :" + Arrays.toString(args));
            //分库分表插入测试数据
            int updates = jdbcTemplate.update(sql, args);
            //单库插入数据,作对比
            jdbcTemplateWithDBCP.update(sql, args);
        }
        System.out.println("cost time :" + ((System.currentTimeMillis() - currentTime) / (1000 * 60)) + "min");
    }

    public static String generateDate() {
        int month = random.nextInt(12) + 1;
        int day = random.nextInt(28) + 1;
        return "1993-" + month + "-" + day;
    }

    private List<SimpleStudent> queryForSimpleStudentList(String sql) throws SQLException {
        List<SimpleStudent> simpleStudents = jdbcTemplate.query(sql, new RowMapper<SimpleStudent>() {
            @Override
            public SimpleStudent mapRow(ResultSet resultSet, int i) throws SQLException {
                SimpleStudent simpleStudent = new SimpleStudent();
                simpleStudent.setId(resultSet.getLong(1));
                simpleStudent.setName(resultSet.getString(2));
                simpleStudent.setAge(resultSet.getInt(3));
                simpleStudent.setBirth(resultSet.getDate(4));
                simpleStudent.setGmt_create(resultSet.getTimestamp(5));
                return simpleStudent;
            }
        });
        return simpleStudents;
    }


    private List<SimpleStudent> queryForSimpleStudentList(String sql, List<Object> args) throws SQLException {
        List<SimpleStudent> simpleStudents = jdbcTemplate.query(sql, args.toArray(), new RowMapper<SimpleStudent>() {
            @Override
            public SimpleStudent mapRow(ResultSet resultSet, int i) throws SQLException {
                SimpleStudent simpleStudent = new SimpleStudent();
                simpleStudent.setId(resultSet.getLong(1));
                simpleStudent.setName(resultSet.getString(2));
                simpleStudent.setAge(resultSet.getInt(3));
                simpleStudent.setBirth(resultSet.getDate(4));
                simpleStudent.setGmt_create(resultSet.getTimestamp(5));
                return simpleStudent;
            }
        });
        return simpleStudents;
    }

    private List<SimpleStudent> queryForSimpleStudentList0(String sql) throws SQLException {
        return jdbcTemplate.query(sql, new ResultSetExtractor<List<SimpleStudent>>() {
            @Override
            public List<SimpleStudent> extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                List<SimpleStudent> simpleStudents = Lists.newLinkedList();
                while (resultSet.next()) {
                    SimpleStudent simpleStudent = new SimpleStudent();
                    simpleStudent.setId(resultSet.getLong(1));
                    simpleStudent.setName(resultSet.getString(2));
                    simpleStudent.setAge(resultSet.getInt(3));
                    simpleStudent.setBirth(resultSet.getDate(4));
                    simpleStudent.setGmt_create(resultSet.getTimestamp(5));
                    simpleStudents.add(simpleStudent);
                }
                return simpleStudents;
            }
        });
    }

    @Test
    public void test_resultType_iterator_结果集没有groupOrderAggregation() throws SQLException {
        String sql = "select * from t_simple_stu where id IN(2814979044114439,2817514718674951,2817516435783687,2817516436586501,2817520222584837,2817516435701764,2817516436406278)";
        List<SimpleStudent> simpleStudents = queryForSimpleStudentList(sql);
        forEachPrint(simpleStudents);
    }

    @Test
    public void test_resultType_orderby_结果集没有groupAggregation() throws SQLException {
        String sql = "select * from t_simple_stu where id IN(2814979044114439,2817514718674951,2817516435783687,2817516436586501,2817520222584837,2817516435701764,2817516436406278)  order by birth limit 2,3";
        List<SimpleStudent> simpleStudents = queryForSimpleStudentList(sql);
        forEachPrint(simpleStudents);
    }

    //ok
    @Test
    public void test_resultType_groupby_结果集没有Aggregation() throws SQLException {
        String sql = "select age,max() from t_simple_stu where id IN(2814979044114439,2817514718674951,2817516435783687,2817516436586501,2817520222584837,2817516435701764,2817516436406278)  group by age limit 2,3";
        List<SimpleStudent> simpleStudents = queryForSimpleStudentList(sql);
        forEachPrint(simpleStudents);
    }

    //ok
    @Test
    public void test_resultType_groupby_aggregation_count() throws SQLException {
        String sql = "select age,count(id) from t_simple_stu where id IN(2814979044114439,2817514718674951,2817516435783687,2817516436586501,2817520222584837,2817516435701764,2817516436406278)  group by age";
        List<SimpleStudent> simpleStudents = queryForSimpleStudentList(sql);
        forEachPrint(simpleStudents);
    }

    //ok
    @Test
    public void test_resultType_groupby_aggregation_maxmin() throws SQLException {
        String sql = "select age,min(id) from t_simple_stu where id IN(2814979044114439,2817514718674951,2817516435783687,2817516436586501,2817520222584837,2817516435701764,2817516436406278)  group by age";
        List<SimpleStudent> simpleStudents = queryForSimpleStudentList(sql);
        forEachPrint(simpleStudents);
    }

    //ok
    @Test
    public void test_resultType_groupby_aggregation_sum() throws SQLException {
        String sql = "select age,sum(id) from t_simple_stu where id IN(2814979044114439,2817514718674951,2817516435783687,2817516436586501,2817520222584837,2817516435701764,2817516436406278)  group by age";
        List<SimpleStudent> simpleStudents = queryForSimpleStudentList(sql);
        forEachPrint(simpleStudents);
    }

    //ok
    @Test
    public void test_resultType_aggregation_sum_without_groupby() throws SQLException {
        String sql = "select count(id),sum(age) from t_simple_stu where id IN(2814979044114439,2817514718674951,2817516435783687,2817516436586501,2817520222584837,2817516435701764,2817516436406278)";
        List<SimpleStudent> simpleStudents = queryForSimpleStudentList(sql);
        forEachPrint(simpleStudents);
    }

    //ok
    @Test
    public void test_resultType_orderby_groupby() throws SQLException {
        String sql = "select sum(age) from t_simple_stu where id IN(2814979044114439,2817514718674951,2817516435783687,2817516436586501,2817520222584837,2817516435701764,2817516436406278) group by age order by age DESC";
        List<SimpleStudent> simpleStudents = queryForSimpleStudentList(sql);
        forEachPrint(simpleStudents);
    }
}
